﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Sql;
using System.Xml.Serialization;

namespace AlterGear.SQLLibrary
{
    [Serializable]
    public class SQLList : IComparable<SQLList>
    {
        [XmlIgnore]
        const string SERVER_NAME = "ServerName";
        [XmlIgnore]
        const string INSTANCE_NAME = "InstanceName";
        [XmlIgnore]
        const string SCHEMA_DATABASES = "Databases";
        [XmlIgnore]
        const string DATABASE_NAME = "Database_name";

        [XmlIgnore]
        const string INDEX_ID = "index_id";
        [XmlIgnore]
        const string OBJECT_ID = "object_id";
        [XmlIgnore]
        const string NAME = "name";
        [XmlIgnore]
        const string AVG_FRAG = "Avg_Fragmentation_In_Percent";
        [XmlIgnore]
        const string TABLE_NAME = "tableName";

        [XmlIgnore]
        const string SQL_INDICES = @"SELECT A.object_id, A.index_id, B.[name], Avg_Fragmentation_In_Percent, C.[name] as tableName 
                                        FROM sys.dm_db_index_physical_stats (db_id(), NULL,NULL, NULL, NULL) AS A 
                                        JOIN sys.indexes B WITH(NOLOCK) ON A.Object_id = B.Object_id 
                                            AND A.Index_id = B.Index_id 
                                        JOIN sys.Tables C WITH(NOLOCK) ON B.Object_id = C.Object_id
                                        WHERE Avg_Fragmentation_In_Percent >= {0}
                                        AND B.[name] IS NOT NULL
                                        order by Avg_Fragmentation_In_Percent desc";
        [XmlIgnore]
        const string SQL_REORGANISE = @"ALTER INDEX [{0}] ON [{1}] REORGANIZE";
        [XmlIgnore]
        const string SQL_REBUILD = @"ALTER INDEX [{0}] ON [{1}] REBUILD";

        [XmlIgnore]
        private System.Data.SqlClient.SqlConnectionStringBuilder sqlConnBuilder;

        
        [XmlAttribute(DataType="string")]
        public string ConnectionString { get; set; }

        public static List<string> GetSqlInstances()
        {
            List<string> lstInstances = new List<string>();

            SqlDataSourceEnumerator sqlEnumerator = SqlDataSourceEnumerator.Instance;
            DataTable dataTable = sqlEnumerator.GetDataSources();

            foreach (DataRow row in dataTable.Rows)
            {
                lstInstances.Add(row[SERVER_NAME] + "\\" + row[INSTANCE_NAME]);
            }
            return lstInstances;
        }

        public static List<string> GetSQLDatabase(string sqlInstance, bool integratedSecurity, string userName, string password)
        {
            List<string> lstDataBases = new List<string>();

            System.Data.SqlClient.SqlConnectionStringBuilder sqlConnBuilder = new System.Data.SqlClient.SqlConnectionStringBuilder();
            sqlConnBuilder.DataSource = sqlInstance;
            sqlConnBuilder.IntegratedSecurity = integratedSecurity;
            sqlConnBuilder.UserID = userName;
            sqlConnBuilder.Password = password;
            string connectionString = sqlConnBuilder.ConnectionString;

            using (System.Data.SqlClient.SqlConnection sqlConn = new System.Data.SqlClient.SqlConnection(connectionString))
            {
                sqlConn.Open();
                DataTable dt = sqlConn.GetSchema(SCHEMA_DATABASES);
                foreach (DataRow row in dt.Rows)
                {
                    lstDataBases.Add(row[DATABASE_NAME].ToString());
                }
            }
            return lstDataBases;
        }

        public bool CanConnect(string sqlInstance, bool integratedSecurity, string userName, string password, string databaseName, int timeout, bool throwException, out Exception exception, out string connectionString)
        {
            bool canConnect = false;
            connectionString = "";
            exception = null;
            System.Data.SqlClient.SqlConnectionStringBuilder sqlConnBuilder = new System.Data.SqlClient.SqlConnectionStringBuilder();
            sqlConnBuilder.DataSource = sqlInstance;
            sqlConnBuilder.IntegratedSecurity = integratedSecurity;
            sqlConnBuilder.UserID = userName;
            sqlConnBuilder.Password = password;
            sqlConnBuilder.InitialCatalog = databaseName;
            sqlConnBuilder.ConnectTimeout = timeout;

            try
            {
                using (System.Data.SqlClient.SqlConnection sqlConn = new System.Data.SqlClient.SqlConnection(sqlConnBuilder.ConnectionString))
                {
                    sqlConn.Open();
                    connectionString = sqlConnBuilder.ConnectionString;
                    this.ConnectionString = connectionString;     
                    canConnect = true;
                }
            }
            catch (Exception ex)
            {
                exception = ex;
                if(throwException == true)
                    throw ex;
            }

            return canConnect;
        }

        
        /*
        public string SQLServerName()
        {
            sqlConnBuilder = new System.Data.SqlClient.SqlConnectionStringBuilder(this.ConnectionString);
            return sqlConnBuilder.DataSource;
        }
        */
        [XmlIgnore]
        public string DisplaySQLConn
        {
            get
            {
                if (!string.IsNullOrEmpty(this.SQLDatabaseName) && !string.IsNullOrEmpty(this.SQLServerName))
                    return this.SQLServerName + " - " + this.SQLDatabaseName;
                else 
                    return "";
            }
            private set { }
        }
        [XmlAttribute]
        public string SQLServerName
        {
            get
            {
                sqlConnBuilder = new System.Data.SqlClient.SqlConnectionStringBuilder(this.ConnectionString);
                return sqlConnBuilder.DataSource;
            }
            set
            { }


        }

        [XmlAttribute]
        public string SQLDatabaseName
        {
            get
            {
                sqlConnBuilder = new System.Data.SqlClient.SqlConnectionStringBuilder(this.ConnectionString);
                return sqlConnBuilder.InitialCatalog;
            }
            set { }
        } 
        /*
        public string SQLDatabaseName()
        {
            sqlConnBuilder = new System.Data.SqlClient.SqlConnectionStringBuilder(this.ConnectionString);
            return sqlConnBuilder.InitialCatalog;
        }
        */
        
        public System.Data.SqlClient.SqlConnectionStringBuilder GetSqlConnectionStringBuilder()
        {
            return new System.Data.SqlClient.SqlConnectionStringBuilder(this.ConnectionString);
        }

        public List<IndexInfo> GetIndices()
        {
            return this.GetIndices(0);
        }

        public List<IndexInfo> GetIndices(int fragPct)
        {
            List<IndexInfo> lstIndexInfo = new List<IndexInfo>();
            using (System.Data.SqlClient.SqlConnection sqlConn = new System.Data.SqlClient.SqlConnection(this.ConnectionString))
            {
                sqlConn.Open();
                System.Data.SqlClient.SqlCommand sqlCommand = sqlConn.CreateCommand();
                sqlCommand.CommandText = string.Format(SQL_INDICES, fragPct);
                System.Data.SqlClient.SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
                while (sqlDataReader.Read())
                {
                    string name = sqlDataReader.GetString(sqlDataReader.GetOrdinal(NAME));
                    string tableName = sqlDataReader.GetString(sqlDataReader.GetOrdinal(TABLE_NAME));
                    int indexId = sqlDataReader.GetInt32(sqlDataReader.GetOrdinal(INDEX_ID));
                    int objectId = sqlDataReader.GetInt32(sqlDataReader.GetOrdinal(OBJECT_ID));
                    double avgFrag = sqlDataReader.GetDouble(sqlDataReader.GetOrdinal(AVG_FRAG));
                    lstIndexInfo.Add(new IndexInfo(objectId, tableName, indexId, name, (decimal)avgFrag));
                }
            }
            return lstIndexInfo;
        }

        public void ReorganiseIndex(IndexInfo indexInfo)
        {
            System.Data.SqlClient.SqlConnectionStringBuilder sqlConnBuilder = new System.Data.SqlClient.SqlConnectionStringBuilder(this.ConnectionString);

            string reorganise = string.Format(SQL_REORGANISE, indexInfo.Name, indexInfo.TableName);

            using (System.Data.SqlClient.SqlConnection sqlConn = new System.Data.SqlClient.SqlConnection(this.ConnectionString))
            {
                sqlConn.Open();
                System.Data.SqlClient.SqlCommand sqlCommand = sqlConn.CreateCommand();
                sqlCommand.CommandText = reorganise;
                sqlCommand.ExecuteNonQuery();
            }
        }

        public void ReBuildIndex(IndexInfo indexInfo)
        {
            System.Data.SqlClient.SqlConnectionStringBuilder sqlConnBuilder = new System.Data.SqlClient.SqlConnectionStringBuilder(this.ConnectionString);

            string reorganise = string.Format(SQL_REBUILD, indexInfo.Name, indexInfo.TableName);

            using (System.Data.SqlClient.SqlConnection sqlConn = new System.Data.SqlClient.SqlConnection(this.ConnectionString))
            {
                sqlConn.Open();
                System.Data.SqlClient.SqlCommand sqlCommand = sqlConn.CreateCommand();
                sqlCommand.CommandText = reorganise;
                sqlCommand.ExecuteNonQuery();
            }
        }



        #region IComparable<SQLList> Members

        public int CompareTo(SQLList other)
        {
            if (this.SQLServerName == other.SQLServerName
                && this.SQLDatabaseName == other.SQLDatabaseName)
            {
                return 0;
            }
            else
                return -1;
        }

        #endregion
    }

    
}
